﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DTO;
using System.Data.SqlClient;
namespace DAL
{
    public class HocLuc_DAL
    {
         private DataProvider _provider = new DataProvider();
	
		    public HocLuc_DAL()
		    {
			    _provider.connect();
		    }
            public void ThemHocLuc(HocLuc_DTO hl)
            {
                string insertCommand = @"INSERT INTO HOCLUC (MaHS, Thang, Nam, MaLKQ) VALUES(" +
                       hl.MaHS + ", " + hl.Thang + "," + hl.Nam + ",'" + hl.MaLKQ + "')";
                       

                _provider.executeNonQuery(insertCommand);
            }
            public List<HocLuc_DTO> DS_HocLucTheoMaHS(int MaHS)
            {
                // Một list các parameter
                List<SqlParameter> paramters = new List<SqlParameter>();
                paramters.Add(new SqlParameter("@MaHS", MaHS));



                // Gọi hàm excute sử dujg procedure có parameter
                SqlDataReader reader = (SqlDataReader)_provider.executeQueryParameter("DS_HocLucTheoMaHS", paramters);

                List<HocLuc_DTO> List_HL = new List<HocLuc_DTO>();

                while (reader.Read())
                {
                    HocLuc_DTO hl = new HocLuc_DTO();
                    hl.MaHS = int.Parse(reader["MaHS"].ToString());
                    hl.Thang = int.Parse(reader["Thang"].ToString());
                    hl.Nam = int.Parse(reader["Nam"].ToString());
                    hl.MaLKQ = reader["MaLKQ"].ToString();
                 
                    List_HL.Add(hl);

                }

                reader.Close();
                return List_HL;
            }
            public List<HocLuc_DTO> DS_HocLuc()
            {

                SqlDataReader reader = (SqlDataReader)_provider.executeQuery("DS_HocLuc");

                List<HocLuc_DTO> List_HL = new List<HocLuc_DTO>();

                while (reader.Read())
                {
                    HocLuc_DTO hl = new HocLuc_DTO();
                    hl.MaHS = int.Parse(reader["MaHS"].ToString());
                    hl.Thang = int.Parse(reader["Thang"].ToString());
                    hl.Nam = int.Parse(reader["Nam"].ToString());
                    hl.MaLKQ = reader["MaLKQ"].ToString();

                    List_HL.Add(hl);

                }

                reader.Close();
                return List_HL;
            }
          
            public void Xoa(HocLuc_DTO hl)
            {
                string deleteCommand = "DELETE FROM HOCLUC WHERE MaHS = " + hl.MaHS;

                _provider.executeNonQuery(deleteCommand);
            }
    }
}
